﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Amie.Models;
using System.Configuration;
using System.Reflection;

namespace Amie.DB
{
    public class SchemaChangeApi
    {
        private PetaPoco.Database _database;
        
        private const string PROVIDER_NAME = "System.Data.SqlClient";
        private const string INITIAL_DATABASE_SCHEMA_FILENAME = "databasebaseline.sql";
        private const string INITIAL_DATABASE_DEFAULTS_FILENAME = "databasedefaults.sql";
        //private string _sqlFilePath = Environment.CurrentDirectory + Path.DirectorySeparatorChar + "Database";
        private ProductInfo _info;

        
        public SchemaChangeApi(ProductInfo info)
        {
            _info = info;        
            _database = new PetaPoco.Database(_info.ConnectionString.ConnectionString, PROVIDER_NAME);
        }

        /// <summary>
        /// This will install a new version of the database or update an older version.  The database must exist in order for this to work. 
        /// If it does not exist the InstallResult.Success will be set to false and there will be a message.
        /// </summary>
        /// <returns></returns>
        public InstallResult CreateDatabase()
        {
            InstallResult result = new InstallResult();

            result = RunDatabaseTests();
            if (!result.Success)
                return result;

            Logger.Info("Current Database Version: {0}", DatabaseVersionInstalled.ToString());
            Logger.Info("Update Script Version: {0}", DatabaseUpdateVersion.ToString());

            if (DatabaseIsNew)
            {
                result = BuildNewDatabase();
                if (!result.Success)
                    return result;
            }

            //Yes we want to run the function above and below.
            //The code (above) creates the initial database which could be 100 versions old.
            //This code (below) will update the database to the lastest version.

            if (!DatabaseIsCurrent)
            {
                Logger.Info("Database exists it's out of date and will be updated.");
                Logger.Info("The following scripts will be ran on the database");
                var scripts = GetScripts();
                foreach (var item in scripts)
                {
                    Logger.Info("{0}.", item.ScriptName);
                }

                foreach (var item in scripts)
                {
                    Logger.Info("Running {0} item.", item.ScriptName);
                    var script = RunSingleScript(item.ScriptName);
                    Logger.Info("Script status: {0}, {1}", script.Status, script.ScriptErrors);
                    if (script.Status == Models.SchemaChange.Status_Failed)
                    {
                        string message = string.Format("The script {0} failed. With message {1}. The script has stopped. \r\n\r\nFix the errors and run it again.", script.ScriptName, script.ScriptErrors);
                        Logger.Info(message);
                        result.Success = false;
                        result.Message = message;
                        return result;
                    }
                }
            }
            else
            {
                Logger.Info("The database is current, nothing to do here.");
            }

            Logger.Info("Database update finished!");
            return result;
        }

        private InstallResult RunDatabaseTests()
        {
            InstallResult result = new InstallResult();

            Logger.Info("Connection string check...");
            if (string.IsNullOrEmpty(_info.ConnectionString.ConnectionString))
            {
                result.Success = false;
                result.Message = "Database connection string missing.";
                return result;
            }

            if (!TestDatabaseConnection().Success)
            {
                result.Success = false;
                result.Message = "Connection to the database failed.";
                return result;
            }

            Logger.Info("Connection string looks good.");
            return result;
        }

        internal InstallResult TestDatabaseConnection()
        {
            InstallResult result = new InstallResult();
            var con = new SqlConnection(_info.ConnectionString.ConnectionString);
            try
            {
                con.Open();
                result.Success = true;
                return result;
            }
            catch (Exception ex)
            {
                result.Message= ex.ToString();
                result.Success = false;
                return result;
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// In order to get this database version the connection string information in ProductInfo has to be correct.  Also the SchemaChange table has to exist in then database.
        /// If either of these missing this will return 0 as the database version.
        /// </summary>
        internal double DatabaseVersionInstalled
        {
            get
            {
                string sql = "SELECT TOP 1 * from SchemaChange ORDER BY DatabaseVersion Desc";
                try
                {
                    var p = _database.SingleOrDefault<SchemaChange>(sql);
                    return p == null ? 0 : p.DatabaseVersion;
                }
                catch (Exception)
                {
                    return 0;
                }
            }

        }

        private List<SchemaChange> GetHistory()
        {
            string sql = "ORDER BY DatabaseVersion";
            var p = _database.Fetch<SchemaChange>(sql);
            return p;
        }

        private List<Models.SchemaChange> GetScripts()
        {
            double databaseVersion = DatabaseVersionInstalled;
            _scriptsToRun = new List<SchemaChange>();
            SearchScriptsToRun(_info.DatabaseUpdatePath, databaseVersion);
            var scripts = _scriptsToRun.OrderBy(x => x.DatabaseVersion).ToList();
            return scripts;
        }

        private InstallResult BuildNewDatabase()
        {
            InstallResult result = new InstallResult();
            result = BuildInitialSchema();
            if (!result.Success) return result;

            result = BuildDatabaseDefaults();

            return result;
        }

        /// <summary>
        /// Looks for the file named DatabaseBaseline.sql and runs it on a blank database.
        /// </summary>
        private InstallResult BuildInitialSchema()
        {
            InstallResult result = new InstallResult();
            Logger.Info("Creating initial database schema from {0}.", INITIAL_DATABASE_SCHEMA_FILENAME);

            FileInfo file = new FileInfo(_info.DatabaseUpdatePath + Path.DirectorySeparatorChar + INITIAL_DATABASE_SCHEMA_FILENAME);
            if (!file.Exists)
            {
                Logger.Error("Initial database schema file {0} could not be found.  Database schema creation failed.", INITIAL_DATABASE_SCHEMA_FILENAME);
                result.Success = false;
                result.SetMessage("The initial schema creation for this database has failed. The " + INITIAL_DATABASE_SCHEMA_FILENAME + " file could not be found at {0}.", file.FullName);
                return result;
            }

            var dbCreateResuls = RunSingleScript(file.Name);
            if (dbCreateResuls.Status == SchemaChange.Status_Failed)
            {
                Logger.Error("The initial schema creation for this database has failed. There was a problem while running the update file {0}. Here is the error: {1} ", file.Name, dbCreateResuls.ScriptErrors);
                result.Success = false;
                result.SetMessage("The initial schema creation for this database has failed. There was a problem while running the update file {0}. Here is the error: {1} ", file.Name, dbCreateResuls.ScriptErrors);
            }

            Logger.Info("Initial database schema created!");
            return result;
        }

        /// <summary>
        /// Looks for a file named DatabaseDefaults.sql.  If it exists it runs it.  The purpose of this file is to create default data in the database.  This will only be ran one time on initial creation of the database.
        /// </summary>
        private InstallResult BuildDatabaseDefaults()
        {
            InstallResult result = new InstallResult();

            Logger.Info("Creating initial database defaults from {0}.", INITIAL_DATABASE_DEFAULTS_FILENAME);

            FileInfo file = new FileInfo(_info.DatabaseUpdatePath + Path.DirectorySeparatorChar + INITIAL_DATABASE_DEFAULTS_FILENAME);
            if (!file.Exists)
            {
                string message = string.Format("Initial database defaults file {0} could not be found.  Database schema creation was successful but the defaults were not loaded into the database.  This will cause a problem with login and other initial date the database is expecting.  Try to to run it again in SQL manager if possible.", file.FullName);

                Logger.Error(message);
                result.Success = false;

                result.SetMessage(message);
                return result;
            }

            var dbCreateResuls = RunSingleScript(file.Name);
            if (dbCreateResuls.Status == SchemaChange.Status_Failed)
            {
                string message = string.Format("Building the initial database defaults for this database has failed. There was a problem while running the update file {0}. Here is the error: {1}. This will cause a problem with login and other initial date the database is expecting.  Try to to run it again in SQL manager if possible.", file.Name, dbCreateResuls.ScriptErrors);
                Logger.Error(message);
                result.Success = false;
                result.SetMessage(message);
            }

            Logger.Info("Initial database defaults created!");

            return result;
        }

        private bool DatabaseIsCurrent
        {
            get
            {
                return DatabaseVersionInstalled == DatabaseUpdateVersion;
            }
        }

        /// <summary>
        /// Returns true if the InstalledDatabaseVersion is 0
        /// </summary>
        private bool DatabaseIsNew
        {
            get
            {
                return DatabaseVersionInstalled == 0;
            }
        }

        /// <summary>
        /// The version of the update files.  The path to thse files is from the ProductInfo.DatabaseUpdatePath property.
        /// </summary>
        internal double DatabaseUpdateVersion
        {
            get
            {
                _fileVersions = new List<double>();
                RetrieveAllFileVersions(_info.DatabaseUpdatePath);

                if (_fileVersions.Count() == 0) return 0;

                return _fileVersions.ToArray().Max();
            }
        }

        private double GetVersionFromFileName(string fileName)
        {
            int separatorIndex = fileName.IndexOf("-");
            //The filename does not contain the required separator - get out.  If the script is REM'd out then set version to 0 and it will never be ran.
            if (separatorIndex == -1 || fileName.StartsWith("REM")) return 0;

            string versionOnFile = fileName.Substring(0, separatorIndex);
            double fileVersion = double.Parse(versionOnFile);
            return fileVersion;
        }

        private Models.SchemaChange BuildSchemaFromFile(FileInfo file, double fileVersion)
        {
            List<string> scriptLines = ExtractSqlFromFile(file.FullName);
            Models.SchemaChange script = new Models.SchemaChange();
            script.DatabaseVersion = file.Name.ToLower() == INITIAL_DATABASE_SCHEMA_FILENAME ? .5 : fileVersion;
            script.LogThisChange = file.Name.ToLower() == INITIAL_DATABASE_DEFAULTS_FILENAME ? false : true;
            script.DateApplied = DateTime.Now;
            script.ScriptName = file.Name;
            script.Status = Models.SchemaChange.Status_NotRan;
            script.Notes = ExtractComments(scriptLines, NOTE_COMMENT_PREFIX);
            script.ScriptContent = BuildScriptContent(scriptLines);
            script.ScriptLines = scriptLines;
            return script;
        }

        private string BuildScriptContent(List<string> fileContents)
        {
            StringBuilder scripts = new StringBuilder();

            foreach (string line in fileContents)
            {
                scripts.AppendLine(line);
            }
            return scripts.ToString();
        }

        public Models.SchemaChange RunSingleScript(string scriptName)
        {
            //Recurse through script tree and find the script that matches this name.
            var script = FetchScript(scriptName);

            if (script == null)
            {
                script = new SchemaChange();
                script.Status = Models.SchemaChange.Status_Failed;
                script.ScriptErrors = string.Format("The script name {0} was not found. The script did not run.", scriptName);
                return script;
            }


            using (SqlConnection connection = new SqlConnection(_info.ConnectionString.ConnectionString))
            {
                connection.Open();

                foreach (var item in script.ScriptLines)
                {
                    using (SqlTransaction transaction = connection.BeginTransaction())
                    {

                        try
                        {
                            SqlCommand sqlCommand = new SqlCommand(item, connection);
                            sqlCommand.Connection = connection;
                            sqlCommand.Transaction = transaction;
                            sqlCommand.ExecuteNonQuery();
                            transaction.Commit();
                        }
                        catch (Exception ex)
                        {
                            string scriptComments = ExtractSingleComment(item, SCRIPT_COMMENT_PREFIX);
                            script.ScriptErrors = string.Format("Failed Running script {0} with the comment {1}.  Error: {2}", script.ScriptName, scriptComments, ex.Message);

                            //try
                            //{
                            //    transaction.Rollback();
                            //}
                            //catch (Exception rollbackException)
                            //{
                            //    script.ScriptErrors += "\r\nThere were also Transaction rollback errors: " + rollbackException.Message;
                            //}

                            //Get out becuase something broke!
                            script.Status = Models.SchemaChange.Status_Failed;
                            return script;
                        }
                    }
                }
                script.Status = Models.SchemaChange.Status_Success;
                if (script.LogThisChange) AddSchemaChange(script);
                return script;
            }
        }

        private Models.SchemaChange FetchScript(string fileName)
        {
            SearchForSpecificScript(_info.DatabaseUpdatePath, fileName);
            if (_specificScript != null)
                return BuildSchemaFromFile(_specificScript, GetVersionFromFileName(_specificScript.Name));
            else
                return null;
        }

        //The next three functions are recursive, just so you know. Sometimes these are landmines.


        //Holds results of the SearchScriptsToRun function
        private List<Models.SchemaChange> _scriptsToRun;
        private void SearchScriptsToRun(string startDir, double version)
        {
            startDir = FixDirectoryPath(startDir);
            string[] files = null;
            files = Directory.GetFileSystemEntries(startDir);
            foreach (string f in files)
            {
                //Sub directories
                if ((Directory.Exists(f)))
                {
                    SearchScriptsToRun(f, version);
                    //Files in directory
                }
                else
                {
                    FileInfo file = new FileInfo(f);
                    double versionOnFile = GetVersionFromFileName(file.Name);
                    if (versionOnFile > 0)
                    {
                        if (versionOnFile > version)
                        {
                            var change = BuildSchemaFromFile(file, versionOnFile);
                            _scriptsToRun.Add(change);
                        }
                    }
                }
            }
        }

        //Holds the result of the SearchForSpecificScript functiond
        private FileInfo _specificScript;
        private void SearchForSpecificScript(string startDir, string fileName)
        {
            startDir = FixDirectoryPath(startDir);
            string[] files = null;
            files = Directory.GetFileSystemEntries(startDir);
            foreach (string f in files)
            {
                //Sub directories
                if ((Directory.Exists(f)))
                {
                    SearchForSpecificScript(f, fileName);
                    //Files in directory
                }
                else
                {
                    FileInfo file = new FileInfo(f);

                    if (file.Name.ToLower() == fileName.ToLower())
                    {
                        _specificScript = file;
                        return;
                    }
                }
            }
        }

        //Holds all of the file versions found.
        private List<double> _fileVersions;
        private void RetrieveAllFileVersions(string startDir)
        {
            startDir = FixDirectoryPath(startDir);
            string[] files = null;
            files = Directory.GetFileSystemEntries(startDir);
            foreach (string f in files)
            {
                //Sub directories
                if ((Directory.Exists(f)))
                {
                    RetrieveAllFileVersions(f);
                    //Files in directory
                }
                else
                {
                    FileInfo file = new FileInfo(f);
                    double versionOnFile = GetVersionFromFileName(file.Name);
                    if (versionOnFile > 0)
                    {
                        _fileVersions.Add(versionOnFile);
                    }

                }
            }
        }

        private static string NOTE_COMMENT_PREFIX = "--@";
        private static string SCRIPT_COMMENT_PREFIX = "--$";

        private string ExtractComments(List<string> fileContents, string delimiter)
        {
            StringBuilder comments = new StringBuilder();

            foreach (string line in fileContents)
            {
                comments.AppendLine(ExtractSingleComment(line, delimiter));
            }

            return comments.ToString();
        }

        private string ExtractSingleComment(string line, string delimiter)
        {
            StringBuilder comments = new StringBuilder();

            string[] lines = line.Split(new[] { "\r\n" }, StringSplitOptions.None);

            foreach (var singleLine in lines)
            {
                int commentLocation = singleLine.IndexOf(delimiter);
                //Grab the comment from the start of the comment (Delimiter) to the end of the line.
                if (commentLocation > -1)
                {
                    int commentStart = commentLocation + 3;
                    int commentEnd = singleLine.Length - commentStart;
                    string comment = singleLine.Substring(commentStart, commentEnd);
                    comments.AppendLine(comment);
                }
            }
            return comments.ToString();
        }

        private List<string> ExtractSqlFromFile(string filePath)
        {
            System.IO.StreamReader sqlFile = new System.IO.StreamReader(filePath);
            string sql = sqlFile.ReadToEnd();
            sqlFile.Close();

            string[] sqls = sql.Split(new string[] { "GO" }, StringSplitOptions.None);
            List<string> sqlList = new List<string>();

            foreach (var item in sqls)
            {
                if (!string.IsNullOrWhiteSpace(item))
                {
                    if (!item.Contains("/*"))
                        sqlList.Add(item);
                }
            }
            return sqlList;
        }

        private static string FixDirectoryPath(string path)
        {
            if (path.Substring(path.Length - 1, 1) != Path.DirectorySeparatorChar.ToString())
            {
                path = path + Path.DirectorySeparatorChar;
            }
            return path;
        }

        private void AddSchemaChange(SchemaChange changeScript)
        {
            if (changeScript.Status == Models.SchemaChange.Status_Success)
            {
                _database.Save(changeScript);
                //changeScript.Save();
            }
        }

        private static List<string> FindConnectionString(string startPath)
        {
            _possibleConnectionString = new List<string>();
            DirectoryInfo di = new DirectoryInfo(startPath);
            SearchForConfigFiles(di.Parent.FullName);
            return _possibleConnectionString;
        }

        //Search for connection string
        private static List<string> _possibleConnectionString;
        private static void SearchForConfigFiles(string startDir)
        {
            startDir = FixDirectoryPath(startDir);
            string[] files = null;
            files = Directory.GetFileSystemEntries(startDir);
            foreach (string f in files)
            {
                //Sub directories
                if ((Directory.Exists(f)))
                {
                    SearchForConfigFiles(f);
                    //Files in directory
                }
                else
                {
                    FileInfo file = new FileInfo(f);
                    if (file.Extension.ToLower() == ".config")
                    {
                        _possibleConnectionString.Add(ExtractConnectionString(file.FullName));
                    }
                }
            }
        }

        private static string ExtractConnectionString(string filePath)
        {
            Utils.Xml xml = new Utils.Xml(filePath);
            return xml.GetConnectionString();
        }
    }
}
